package com.siemens.industrial.web.mvc.dao.partner;

import com.siemens.industrial.model.partner.DeviceStatePeriod;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.sql.Types;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by qupen on 2016/8/31.
 */
@Service
public class PartnerDeviceStatePeriodDao {
    @Resource
    JdbcTemplate jdbcTemplate;

    // consider shift, investgate if it in plan

    private static final String databaseTag = "{database}";
    private static final String getDeviceStatePeriodQuery = "SELECT * FROM `" + databaseTag + "`.`device_state_period` WHERE `device_id` = ? AND `start_time` = ?";
    private static final String getDeviceStatePeriodInDeviceQuery = "SELECT * FROM `" + databaseTag + "`.`device_state_period` WHERE `device_id` = ? ORDER BY `start_time` DESC LIMIT 1";
    private static final String getDeviceStatePeriodsInDeviceQuery = "SELECT * FROM `" + databaseTag + "`.`device_state_period` WHERE `device_id` = ? ORDER BY `start_time` DESC";
    private static final String getDeviceStatePeriodsInTimeQuery = "SELECT * FROM `" + databaseTag + "`.`device_state_period` WHERE `start_time` = ?";
    private static final String getDeviceStatePeriodsQuery = "SELECT * FROM `" + databaseTag + "`.`device_state_period`";
    private static final String saveDeviceStatePeriodQuery = "INSERT INTO `" + databaseTag + "`.`device_state_period` (`device_id`, `start_time`, `end_time`, `state`) VALUES (?,?,?,?)";
    private static final String updateDeviceStatePeriodQuery = "UPDATE `" + databaseTag + "`.`device_state_period` SET `end_time` = ? WHERE `device_id` = ? AND `start_time` = ?";
    private static final String getPeriodsoftime = "SELECT \n" +
            "    ((UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time))) minute,\n" +
            "    a.*\n" +
            "FROM\n" +
            "    `" + databaseTag + "`.device_state_period a\n" +
            "WHERE\n" +
            "    start_time >= ?\n" +
            "        AND end_time <= ?\n" +
            "        AND device_id = ?\n" +
            "ORDER BY start_time;";

    //  private static final String getPeriodsoftime = "SELECT CAST(((UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) / 60) AS SIGNED) minute,a.* FROM `97248dd9-c6a3-436a-aa53-ed767cc8673d`.device_state_period a WHERE start_time >'2016-11-03T00:00:00' AND end_time < '2016-11-04T00:00:00' AND device_id = '944c0cb4-0a77-4791-af0c-2e8fc75a7ffa' ORDER BY start_time;\n";
    //得到一天的班次区间段 如 00:02:00 - 00:04:00,00:04:00 - 00:06:00  and so on
    private static final String getTimePeriod = "SELECT start_time as startTime,end_time as endTime FROM `"+databaseTag+"`.device_shift_relation WHERE DATE(begin_date) <= DATE(?) and date(end_date) >= date(?) and plan_state = 1 ORDER BY end_time;";
    //得到一个班次的状态
    /// TODO: 2016/11/9 数据需要处理
//    getTimePeriodState.replace(databaseTag, database), startTime, startTime, endDate,end_time, endDate,end_time, deviceId, startTime, endDate,end_time,startTime,startTime);
    private static final String getTimePeriodState = "SELECT \n" +
            "   b.device_id,sum(unix_timestamp(b.end_time)-unix_timestamp(b.start_time)) as time,b.state\n" +
            "   FROM\n" +
            "   (SELECT \n" +
            "       a.device_id,\n" +
            "           CASE\n" +
            "               WHEN a.start_time < date_add(?, interval ? hour_second) THEN date_add(?, interval ? hour_second)\n" +
            "               ELSE a.start_time\n" +
            "           END AS start_time,\n" +
            "           CASE\n" +
            "               WHEN a.end_time > date_add(?, interval ? hour_second) THEN date_add(?, interval ? hour_second)\n" +
            "               ELSE a.end_time\n" +
            "           END AS end_time,\n" +
            "           a.state\n" +
            "   FROM\n" +
            "       `"+databaseTag+"`.device_state_period a\n" +
            "   WHERE\n" +
            "       a.device_id = ?\n" +
            "           AND(" +
            "               (a.start_time > date_add(?, interval ? hour_second) AND a.start_time < date_add(?, interval ? hour_second))\n" +
            "           OR " +
            "                (a.start_time <= date_add(?, interval ? hour_second) AND a.end_time > date_add(?, interval ? hour_second))" +
            "           )) b group by b.state,b.device_id;";

    //获取当天及前一天最后一条数据
    private static final String getDeviceStates = "SELECT a.device_id,a.time as start_time,a.state\n" +
            "\tFROM\n" +
            "           `"+databaseTag+"`.device_state a\n" +
            "            WHERE\n" +
            "                a.device_id = ? \n" +
            "                    AND (DATE(a.time) = ?\n" +
            "                    AND a.status_type = '0'\n" +
            "                    OR (a.time = (SELECT \n" +
            "                       MAX(b.time)\n" +
            "                    FROM\n" +
            "                        `"+databaseTag+"`.device_state b\n" +
            "                    WHERE\n" +
            "                        DATE(date_add(b.time,INTERVAL 1 DAY)) = ?)))\n" +
            "            ORDER BY time;";

    //一天的状态饼图
    private static final String getOneDayState = "select sum(UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time)) 'time',state from `" + databaseTag + "`.device_state_period where start_time>=? and end_time<=? and device_id=? group by state;";
    private static final String insertDownTime = "INSERT INTO "+databaseTag+".`device_state_period` (`device_id`, `start_time`, `end_time`, `state`) VALUES (?, ?, ?, '-1');";
    public DeviceStatePeriod getDeviceStatePeriod(String database, String deviceId, java.util.Date startTime) {
        try {
            return jdbcTemplate.queryForObject(getDeviceStatePeriodQuery.replace(databaseTag, database), new Object[]{deviceId, new java.sql.Date(startTime.getTime())}, new int[]{Types.VARCHAR, Types.DATE}, DeviceStatePeriod.getRowMapper());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public DeviceStatePeriod getDeviceStatePeriodInDevice(String database, String deviceId) {
        try {
            return jdbcTemplate.queryForObject(getDeviceStatePeriodInDeviceQuery.replace(databaseTag, database), new Object[]{deviceId}, new int[]{Types.VARCHAR}, DeviceStatePeriod.getRowMapper());
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public List<DeviceStatePeriod> getDeviceStatePeriods(String database) {
        return jdbcTemplate.query(getDeviceStatePeriodsQuery.replace(databaseTag, database), DeviceStatePeriod.getRowMapper());
    }

    public List<DeviceStatePeriod> getDeviceStatePeriodsInTime(String database, java.util.Date startTime) {
        return jdbcTemplate.query(getDeviceStatePeriodsInTimeQuery.replace(databaseTag, database), new Object[]{new java.sql.Date(startTime.getTime())}, DeviceStatePeriod.getRowMapper());
    }

    public List<DeviceStatePeriod> getDeviceStatePeriodsInDevice(String database, String deviceId) {
        return jdbcTemplate.query(getDeviceStatePeriodsInDeviceQuery.replace(databaseTag, database), new Object[]{deviceId}, DeviceStatePeriod.getRowMapper());
    }

    public void saveDeviceStatePeriod(String database, DeviceStatePeriod deviceStatePeriod) {
        jdbcTemplate.update(saveDeviceStatePeriodQuery.replace(databaseTag, database), pss -> {
                    pss.setString(1, deviceStatePeriod.getDeviceId());
                    pss.setTimestamp(2, new java.sql.Timestamp(deviceStatePeriod.getStartTime().getTime()));
                    pss.setTimestamp(3, new java.sql.Timestamp(deviceStatePeriod.getEndTime().getTime()));
                    pss.setInt(4, deviceStatePeriod.getState());
                }
        );
    }

    public void updateDeviceStatePeriod(String database, DeviceStatePeriod deviceStatePeriod) {
        jdbcTemplate.update(updateDeviceStatePeriodQuery.replace(databaseTag, database), pss -> {
                    pss.setDate(1, new java.sql.Date(deviceStatePeriod.getEndTime().getTime()));
                    pss.setString(2, deviceStatePeriod.getDeviceId());
                    pss.setDate(3, new java.sql.Date(deviceStatePeriod.getStartTime().getTime()));
                }
        );
    }

//    public List<Map<String, Object>> getPeriodsoftime(String database, Date startTime, Date endTime, String deviceId) {
//        List<Map<String, Object>> list = jdbcTemplate.queryForList(getPeriodsoftime.replace(databaseTag, database), startTime, endTime, deviceId);
//        return list;
//    }

    public List<Map<String, Object>> getPeriodsoftime(String database, Date startTime, Date endTime, String deviceId) {
        return jdbcTemplate.queryForList(getPeriodsoftime.replace(databaseTag, database), startTime, endTime, deviceId);
    }

    //查询设备状态
    public List<Map<String, Object>> getDeviceStates(String database, String deviceId, String startTime) {
        return jdbcTemplate.queryForList(getDeviceStates.replace(databaseTag, database), deviceId,startTime,startTime);
    }

    public List<Map<String, Object>> findChartOneData(String sql) {

        return jdbcTemplate.queryForList(sql);
    }

    /**
     * 查找班次时间段
     *
     * @param database
     * @param startTime such as 2016-11-05
     * @return
     */
    public List<Map<String, Object>> findPeriodsofTime(String database, Date startTime) {
        return jdbcTemplate.queryForList(getTimePeriod.replace(databaseTag, database), startTime, startTime);
    }

    /**
     * 一个班次或一天的状态
     *
     * @param database
     * @param day
     * @param start_time
     * @param end_time 结束时间拆开 原因为 若有24:00:00比第二天的00:00:00 小
     * @return
     */
    public List<Map<String, Object>> findTimePeriodState(String database,String startDay, String day,String start_time, String end_time, String deviceId) {
        return jdbcTemplate.queryForList(getTimePeriodState.replace(databaseTag, database), startDay,start_time,startDay, start_time, day,end_time, day,end_time, deviceId,startDay, start_time, day,end_time,startDay, start_time,startDay, start_time);
    }

    public List<Map<String, Object>> findOneDayState(String database, Date startTime, Date endTime, String deviceId) {
        return jdbcTemplate.queryForList(getOneDayState.replace(databaseTag, database), startTime, endTime, deviceId);
    }

    @Transactional
    public void insertDownTime(String database, Date startTime, Date endTime, String deviceId) {
        jdbcTemplate.update(insertDownTime.replace(databaseTag, database), startTime, endTime, deviceId);
    }
}
